package dao;

import java.sql.*;
import java.util.*;//引用其中的list与Arraylist
import model.*;


public class RecordDao {
	private static RecordDao recordDao=new RecordDao();
	private RecordDao() {}
	public static RecordDao getinstance() {
		return recordDao;
	}
	//插入新记录
	public boolean insertRecord(Record record) {
		String sql="insert into Record(uid,rid,rentTime,returnTime) values(?,?,?,?)";
		Connection conn=BaseConn.getConn();
		PreparedStatement pstm=null;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, record.getUid());
			pstm.setInt(2, record.getRid());
			pstm.setObject(3,record.getRentTime());
			pstm.setObject(4, record.getReturnTime());
			return pstm.executeUpdate() >0;
		}catch(SQLException e){ 
			e.printStackTrace();
		}finally {
			try {
				pstm.close();
				conn.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	//根据用户账号查找记录，用于退房时，修改退房时间
	public boolean updateRecord(int uid,int rid,String returnTime) {
		String sql="update Record set returnTime=? where uid=? and rid=?";
		Connection conn=BaseConn.getConn();
		PreparedStatement pstm=null;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setObject(1, returnTime);
			pstm.setObject(2, uid);
			pstm.setObject(3, rid);
			return pstm.executeUpdate() >0;
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pstm.close();
				conn.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	

//用于ui中的JTable中。功能：每个用户登陆后查看自己的入住记录
public Object[][] getRecordsByUid(int uid) {
    String sql = "select Record.* , name  from Record, U " +
    		"where Record.uid=U.uid and U.uid="+uid;
    Connection conn = BaseConn.getConn();
    ResultSet res = null;
    List<Record> list = new ArrayList<Record>();//用list不用数组的原因：可以将ArrayList想象成一种“会自动扩增容量的Array”。
    PreparedStatement pstm = null;
    try {
        pstm = conn.prepareStatement(sql);
        res = pstm.executeQuery();
        while (res.next()) {
            String name = res.getString("name");
            int rid=res.getInt("rid");
            String rentTime = res.getString("rentTime");
            String returnTime = res.getString("returnTime");
            Record rd = new Record(0,rid,rentTime,returnTime);
            rd.setName(name);
            list.add(rd);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
        	res.close();
        	pstm.close();  
        	conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    Object[][] obj = new Object[list.size()][5];//ArrayList集合类就只能以类作为他的存储对象
    
    for (int i=0;i<list.size();i++) {
        obj[i][0] = i+1;//序号
        obj[i][1] = list.get(i).getName(); //自动装箱
        obj[i][2]=list.get(i).getRid(); 
        obj[i][3] = list.get(i).getRentTime();
        obj[i][4] = list.get(i).getReturnTime();
        
    }
    return obj;
}
//查看用户所有记录
public Object[][] queryAllRecord(){
      String sql="select Record.*,name from Record,U where Record.uid=U.uid";
      Connection conn=BaseConn.getConn();
      List<Record> list=new ArrayList<Record>();
      Statement stmt=null;
      ResultSet res=null;
      try {
    	  stmt=conn.createStatement();
    	  res=stmt.executeQuery(sql);
    	  while (res.next()) {
              String name = res.getString("name");
              int rid=res.getInt("rid");
              String rentTime = res.getString("rentTime");
              String returnTime = res.getString("returnTime");
              Record rd = new Record(0,rid,rentTime,returnTime);
              rd.setName(name);
              list.add(rd);
          }
      } catch (SQLException e) {
          e.printStackTrace();
      } finally {
          try {
          	res.close();
          	stmt.close();  
          	conn.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
      Object[][] obj = new Object[list.size()][5];//ArrayList集合类就只能以类作为他的存储对象

      for (int i=0;i<list.size();i++) {
          obj[i][0] = i + 1;//序号
          obj[i][1] = list.get(i).getName(); //自动装箱
          obj[i][2]=list.get(i).getRid(); 
          obj[i][3] = list.get(i).getRentTime();
          obj[i][4] = list.get(i).getReturnTime();
          
      }
      return obj;

}
public Record queryRecord(int uid,int rid) {
    String sql = "select * from Record where uid="+uid+" and rid="+rid+"";
    Connection conn = BaseConn.getConn();
    ResultSet res = null;
    PreparedStatement pstm = null;
    Record rd=null;
    try {
        pstm = conn.prepareStatement(sql);
        res = pstm.executeQuery();
        while (res.next()) {
        	int u=res.getInt("uid");
            int r=res.getInt("rid");
            String rentTime = res.getString("rentTime");
            String returnTime = res.getString("returnTime");
            	rd = new Record(u,r,rentTime,returnTime);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
        	res.close();
        	pstm.close();  
        	conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return rd;
	
}

}

